package com.ls.mysql.dao;

import com.ls.mysql.entity.YzUsers;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.type.JdbcType;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Map;

/**
 * 用户方法接口
 */
@Mapper
@Component
public interface UserDao {

    //用户登陆
    @Select({"<script>",
            "   select ys.id stid, status, PowerLevel,",
            "   u.id uid, realname, username, password, qq, email, wechat, mobile, address, age, sex, derive, hobby, status_id, u_img,",
            "   su.id spid, surplus,",
            "   m.id meid, MerchName, MerchNo, Classify, Merchant, Introduce, RetailPrice, SurpAmount, product_user_id, m_img,",
            "   ymuo.id moid, OrderAmount, TotalPrice, user_id, merch_id, OrderTime",
            "   from yz_status ys inner join (yz_users u inner join (yz_surplus su left join (yz_merch m left join yz_merch_user_order ymuo on m.id = ymuo.merch_id) on su.id=m.product_user_id)on u.id=su.id) on u.status_id = ys.id",
            "   where u.username=#{account} and u.password=#{password}",
            "</script>"})
    @Results(id = "YzUserMap", value = {
            @Result(column = "uid", property = "id", jdbcType = JdbcType.VARCHAR, id = true),
            @Result(column = "realname", property = "realname", jdbcType = JdbcType.INTEGER),
            @Result(column = "username", property = "username", jdbcType = JdbcType.VARCHAR),
            @Result(column = "password", property = "password", jdbcType = JdbcType.VARCHAR),
            @Result(column = "email", property = "email", jdbcType = JdbcType.VARCHAR),
            @Result(column = "wechat", property = "wechat", jdbcType = JdbcType.VARCHAR),
            @Result(column = "mobile", property = "mobile", jdbcType = JdbcType.VARCHAR),
            @Result(column = "address", property = "address", jdbcType = JdbcType.VARCHAR),
            @Result(column = "age", property = "age", jdbcType = JdbcType.INTEGER),
            @Result(column = "sex", property = "sex", jdbcType = JdbcType.VARCHAR),
            @Result(column = "derive", property = "derive", jdbcType = JdbcType.VARCHAR),
            @Result(column = "hobby", property = "hobby", jdbcType = JdbcType.VARCHAR),
            @Result(column = "u_img", property = "u_img", jdbcType = JdbcType.VARCHAR),
            @Result(column = "stid", property = "yzStatus", one = @One(select = "com.ls.mysql.dao.StatusDao.queryStatusById")),
            @Result(column = "spid", property = "yzSurpluses", one = @One(select = "com.ls.mysql.dao.SurplusDao.querySurplusById")),
            @Result(column = "meid", property = "yzMerch", many = @Many(select = "com.ls.mysql.dao.MerchDao.findMerchById")),
            @Result(column = "moid", property = "yzMerchUserOrders", many = @Many(select = "com.ls.mysql.dao.OrderMapper.queryOrderById"))
    })
    public YzUsers UserLogin(@Param("account") String account, @Param("password") String password);

    //用户注册
    @Insert({"<script>",
            "   insert into yz_users(id,realname, username, password",
            "   <if test=\"user.qq!=null\">,qq</if>",
            "   <if test=\"user.email!=null\">,email</if>",
            "   <if test=\"user.wechat!=null\">,wechat</if>",
            "   <if test=\"user.mobile!=null\">,mobile</if>",
            "   <if test=\"user.address!=null\">,address</if>",
            "   <if test=\"user.age!=null\">,age</if>",
            "   <if test=\"user.sex!=null\">,sex</if>",
            "   <if test=\"user.derive!=null\">,derive</if>",
            "   <if test=\"user.hobby!=null\">,hobby</if>",
            "   <if test=\"user.yzStatus!=null\">,status_id</if>",
            "   <if test=\"user.u_img!=null\">,u_img</if>)",
            "   values(#{user.id},#{user.realname},#{user.username},#{user.password}",
            "   <if test=\"user.qq!=null\">,#{user.qq}</if>",
            "   <if test=\"user.email!=null\">,#{user.email}</if>",
            "   <if test=\"user.wechat!=null\">,#{user.wechat}</if>",
            "   <if test=\"user.mobile!=null\">,#{user.mobile}</if>",
            "   <if test=\"user.address!=null\">,#{user.address}</if>",
            "   <if test=\"user.age!=null\">,#{user.age}</if>",
            "   <if test=\"user.sex!=null\">,#{user.sex}</if>",
            "   <if test=\"user.derive!=null\">,#{user.derive}</if>",
            "   <if test=\"user.hobby!=null\">,#{user.hobby}</if>",
            "   <if test=\"user.yzStatus!=null\">,#{user.yzStatus.id}</if>",
            "   <if test=\"user.u_img!=null\">,#{user.u_img}</if>)",
            "</script>"})
    public int UserRegister(@Param("user") YzUsers user);

    //判断user_id是否存在
    @Select("select count(u.id) from yz_users u where u.id=#{id}")
    public int isExistUser(@Param("id") String UUID);

    //用户注册使用，登陆的账号username必须唯一：检索是否存在
    @Select("select count(u.username) from yz_users u where u.username=#{username}")
    public int isExistUsername(@Param("username") String username);

    //用户修改个人信息
    @Update({"<script>",
            "   update yz_users",
            "   <set>",
            "       <if test=\"user.realname!=null\">realname= #{user.realname},</if>",
            "       <if test=\"user.password!=null\">password=#{user.password},</if>",
            "       <if test=\"user.qq!=null\">qq=#{user.qq},</if>",
            "       <if test=\"user.email!=null\">email=#{user.email},</if>",
            "       <if test=\"user.wechat!=null\">wechat=#{user.wechat},</if>",
            "       <if test=\"user.age!=null\">age=#{user.age},</if>",
            "       <if test=\"user.sex!=null\">sex=#{user.sex},</if>",
            "       <if test=\"user.derive!=null\">derive=#{user.derive},</if>",
            "       <if test=\"user.hobby!=null\">hobby=#{user.hobby},</if>",
            "       <if test=\"user.yzStatus!=null\">status_id=#{user.yzStatus.id},</if>",
            "       <if test=\"user.u_img!=null\">u_img=#{user.u_img},</if>",
            "       <if test=\"user.mobile!=null\">mobile=#{user.mobile},</if>",
            "       <if test=\"user.address!=null\">address=#{user.address},</if>",
            "   </set>",
            "   <where>",
            "       <choose>",
            "           <when test=\"user.id!=null\">",
            "               and yz_users.id = #{user.id}",
            "           </when>",
            "           <otherwise>",
            "               and 1 = 2",
            "           </otherwise>",
            "       </choose>",
            "   </where>",
            "</script>"})
    public int updateUser(@Param("user") YzUsers user);

    //删除用户
    @Delete({"<script>",
            "   delete from yz_users",
            "   <where>",
            "       <if test=\"id!=null\">",
            "           and id=#{id}",
            "       </if>",
            "   </where>",
            "</script>"})
    public int deleteUser(@Param("id") String user_id);

    //查询所有用户，分页&计算用户总数
    @Select({"<script>",
            "   select ys.id stid, status, PowerLevel,",
            "   u.id uid, realname, username, password, qq, email, wechat, mobile, address, age, sex, derive, hobby, status_id, u_img,",
            "   su.id spid, surplus",
            "   from yz_status ys inner join (yz_users u inner join yz_surplus su on u.id=su.id) on u.status_id = ys.id",
            "   limit #{index},#{maxResult}",
            "</script>"})
    @Results(id = "YzUserMaps", value = {
            @Result(column = "uid", property = "id", jdbcType = JdbcType.VARCHAR, id = true),
            @Result(column = "realname", property = "realname", jdbcType = JdbcType.INTEGER),
            @Result(column = "username", property = "username", jdbcType = JdbcType.VARCHAR),
            @Result(column = "password", property = "password", jdbcType = JdbcType.VARCHAR),
            @Result(column = "email", property = "email", jdbcType = JdbcType.VARCHAR),
            @Result(column = "wechat", property = "wechat", jdbcType = JdbcType.VARCHAR),
            @Result(column = "mobile", property = "mobile", jdbcType = JdbcType.VARCHAR),
            @Result(column = "address", property = "address", jdbcType = JdbcType.VARCHAR),
            @Result(column = "age", property = "age", jdbcType = JdbcType.INTEGER),
            @Result(column = "sex", property = "sex", jdbcType = JdbcType.VARCHAR),
            @Result(column = "derive", property = "derive", jdbcType = JdbcType.VARCHAR),
            @Result(column = "hobby", property = "hobby", jdbcType = JdbcType.VARCHAR),
            @Result(column = "u_img", property = "u_img", jdbcType = JdbcType.VARCHAR),
            @Result(column = "stid", property = "yzStatus", one = @One(select = "com.ls.mysql.dao.StatusDao.queryStatusById")),
            @Result(column = "spid", property = "yzSurpluses", one = @One(select = "com.ls.mysql.dao.SurplusDao.querySurplusById"))

    })
    public List<YzUsers> queryAllUsers(@Param("index") Integer index, @Param("maxResult") Integer maxResult);

    @Select("select count(*) from yz_users")
    public int queryTotalUser();

    // 查询所有用户
    @Select({"<script>",
            "   select ys.id stid, status, PowerLevel,",
            "   u.id uid, realname, username, password, qq, email, wechat, mobile, address, age, sex, derive, hobby, status_id, u_img,",
            "   su.id spid, surplus",
            "   from yz_status ys inner join (yz_users u inner join yz_surplus su on u.id=su.id) on u.status_id = ys.id",
            "</script>"})
    @ResultMap("YzUserMaps")
    public List<YzUsers> findAllUsers();

    //批量插入用户
    @Insert({"<script>",
            "   insert into yz_users(id, realname, username, password, qq, email, wechat, mobile, address, age, sex, derive, hobby, status_id)",
            "   values",
            "   <foreach collection=\"userlist\" item=\"user\" separator=\",\">",
            "       (#{user.id},#{user.realname},#{user.username},#{user.password},#{user.qq},#{user.email},#{user.wechat},#{user.mobile},#{user.address},#{user.age},#{user.sex},#{user.derive},#{user.hobby},#{status})",
            "   </foreach>",
            "</script>"})
    public int BatchInsertUsers(Map<String, Object> map);

    //批量查询用户List<user_id>
    @Select({"<script>",
            "   select ys.id stid, status, PowerLevel,",
            "   u.id uid, realname, username, password, qq, email, wechat, mobile, address, age, sex, derive, hobby, status_id, u_img,",
            "   su.id spid, surplus",
            "   from yz_status ys inner join (yz_users u inner join yz_surplus su on u.id=su.id) on u.status_id = ys.id",
            "   where u.username in",
            "   <foreach collection=\"list\" open=\"(\" separator=\",\" close=\")\" item=\"Username\" index=\"i\">",
            "       #{Username}",
            "   </foreach>",
            "</script>"})
    @ResultMap(value = "YzUserMaps")
    List<YzUsers> BatchFind(List<String> user_ids);

    //清空用户，truncate不能用在有关联的表，故用delete
    @Delete("delete from yz_users")
    int deleteFrom();

    //根据id查询用户，核心配置文件应用别名
    @Select("select * from yz_users u where u.id=#{user_id}")
    YzUsers queryUserById(String user_id);
}
